﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using Agresso.Interface.Layer.Domain;
using System.Data;

namespace Agresso.Interface.Layer.Service
{
    public class BunkerGRNService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AlgBatcInputService m_AlgBatcInputService;
        GradeAccountMappingService m_gradeAccountMappingService;
        ConfigurationSettingService m_configSettingService;
        ChargeTypeAccountMappingService m_ChargeTypeAccountMappingService;
        BunkerPOService m_BunkerPOService;
        ProfitCentreService m_profitCentreService;
        VesselService m_vesselService;
        ud_voyageimp_Service m_ud_voyageimp_Service;
        BunkerInventoryManagerMappingService m_BunkerInventoryManagerMappingService;
        ResponsibleUserService m_ResponsibleUserService;

        public BunkerGRNService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_gradeAccountMappingService = new GradeAccountMappingService();
            m_AlgBatcInputService = new AlgBatcInputService();
            m_configSettingService = new ConfigurationSettingService();
            m_ChargeTypeAccountMappingService = new ChargeTypeAccountMappingService();
            m_BunkerPOService = new BunkerPOService();
            m_profitCentreService = new ProfitCentreService();
            m_vesselService = new VesselService();
            m_ud_voyageimp_Service = new ud_voyageimp_Service();
            m_BunkerInventoryManagerMappingService = new BunkerInventoryManagerMappingService();
            m_ResponsibleUserService = new ResponsibleUserService();
        }
        public void ExecuteBunkerGRNXXX()
        {
            try
            {
                IList bunkerGRNs = new ArrayList();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                SqlCommand cmd = new SqlCommand(BunkerGRNSQL.GetAllPendingBunkerGRNTemp(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerGRN grn = new BunkerGRN(rd, true);
                    bunkerGRNs.Add(grn);
                }
                rd.Close();
                //cmd = new SqlCommand(AgressoSQL.GetCustomerByName(), m_dataBaseConectivity.SoftmarSqlConnection);
                string batchID="";
                int enquiry_no = 0;
                foreach (BunkerGRN grn in bunkerGRNs)
                {
                    grn.GradeAccountMapping = m_gradeAccountMappingService.GetGradeAccountMapping(grn.grade);
                    if (grn.GradeAccountMapping.account == "")
                    {
                        UpdateRemarks(grn.i_bunkergrn_id, string.Format("- Unable to find Grade Mapping Item '{0}' \r\n", grn.grade),"0", "");
                        continue;
                    }
                    cmd.Parameters.Clear();
                    cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                    cmd.CommandText = AgressoSQL.GetPurchaseOrderByExtraOrderRefAndSupArticle();
                    cmd.Parameters.Add("@ext_ord_ref", SqlDbType.NVarChar).Value = grn.enquiry_no + "_" + grn.enquiry_Date.ToString("ddMMyyyy"); //grn.enquiry_no + "-" + grn.enquiry_Date.ToString("yyyyMMdd");
                    cmd.Parameters.Add("@sup_article", SqlDbType.NVarChar).Value = grn.GradeAccountMapping.account;
                    rd = cmd.ExecuteReader();
                    apodetail podetail = null;
                    while (rd.Read())
                    {
                        podetail = new apodetail(rd);
                    }
                    rd.Close();
                    if (podetail == null)
                    {
                        UpdateRemarks(grn.i_bunkergrn_id, string.Format("- Unable to find PO for GRN with enquiry No : '{0}' \r\n", grn.enquiry_no), "0", "");
                        continue;
                    }
                    
                    //same batch for same enquiry number -------------------------------
                    batchID = batchID == "" ? "SG" + DateTime.Now.ToString("ddMMyyyyHHmmss") : batchID;
                    if (enquiry_no != grn.enquiry_no)
                        batchID = "SG" + DateTime.Now.ToString("ddMMyyyyHHmmss");
                    //same batch for same enquiry number -------------------------------

                    algbatchinput algbatchinput_GRN = new algbatchinput();
                    algbatchinput_GRN.batch_id = batchID;
                    algbatchinput_GRN.client = podetail.client;
                    algbatchinput_GRN.deliv_date = grn.delivery_Date;
                    algbatchinput_GRN.ext_ord_ref = podetail.ext_ord_ref;
                    algbatchinput_GRN.line_no = podetail.line_no;
                    algbatchinput_GRN.location = "";
                    algbatchinput_GRN.long_info1 = "";
                    algbatchinput_GRN.lot = "";
                    algbatchinput_GRN.order_id = podetail.order_id;
                    algbatchinput_GRN.serial_no = "";
                    algbatchinput_GRN.trans_type = "51";
                    algbatchinput_GRN.value_1 = grn.quantity_delivered;
                    algbatchinput_GRN.voucher_type = "SG";
                    algbatchinput_GRN.warehouse = "";
                    enquiry_no = grn.enquiry_no;                   

                    m_AlgBatcInputService.Insert(algbatchinput_GRN);
                    m_AlgBatcInputService.InsertToInterfaceTable(algbatchinput_GRN);

                    UpdateRemarks(grn.i_bunkergrn_id, algbatchinput_GRN.agrtid.ToString(),"1", algbatchinput_GRN.batch_id);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();

            }

        }
        public void ExecuteBunkerGRNByPO()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                IList pendingPOs = new ArrayList();
                SqlCommand cmd = new SqlCommand(BunkerGRNSQL.GetAllPendingPOTemp(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    apodetail po = apodetail.apodetailFromInterface(rd);
                    pendingPOs.Add(po);
                }
                rd.Close();

                string batchID = "";
                string batchID_IR = "";
                int enquiry_no = 0;

               
                foreach (apodetail podetail in pendingPOs)
                {
                   // if (podetail.ext_ord_ref != "778_03072013") continue;
                    if (podetail.status.Trim() != "O")
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, "-[E022] PO status waiting to order ", "0", "", podetail.remarks);
                        continue;
                    }
                    //GradeAccountMapping acc = m_gradeAccountMappingService.GetGradeAccountMappingByAccount(podetail.sup_article);
                    GradeAccountMapping acc = m_gradeAccountMappingService.GetGradeAccountMapping(podetail.art_descr.Trim());
                    string enquiry = "";
                    try
                    {
                        enquiry = podetail.ext_ord_ref.Split('_')[0];//get enquiry number
                    }
                    catch { }
                    if (enquiry == "")
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, string.Format("-[E019] Unable to find Enquiry Number '{0}' \r\n", podetail.ext_ord_ref), "0", "", podetail.remarks);
                        continue;
                    }
                    if (acc.account_grn == "")
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, string.Format("-[E019] Unable to find GRN Grade Mapping Item '{0}' \r\n", podetail.sup_article), "0", "", podetail.remarks);
                        continue;
                    }
                    cmd.CommandText = BunkerGRNSQL.GetAllBunkerGRNTempByEnquiryNo();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@enquiry_no", SqlDbType.Int).Value = enquiry;
                    cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = podetail.art_descr;// acc.grade;
                    rd = cmd.ExecuteReader();
                    BunkerGRN grn = null;
                    while (rd.Read())
                    {
                        grn = new BunkerGRN(rd, true);
                    }
                    rd.Close();
                    if (grn == null) 
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, string.Format("-[E023] Unable to find GRN for PO:'{0}' item: '{1}' - '{2}' \r\n", podetail.order_id, podetail.sup_article, podetail.art_descr), "0", "", podetail.remarks);
                        continue;
                    }

                    //----------------------------------------IR validation----------------------------------------------------------
                    string message = "";
                    BunkerPO bunkerPO = m_BunkerPOService.GetPOByEnquiryStemed(grn.enquiry_no, grn.grade);
                    ProfitCentre profitCentre = null;
                    afxvoyage afxvoyageVoyage = null;
                    if(bunkerPO !=null)
                        profitCentre = m_profitCentreService.GetProfitCentreMapping(bunkerPO.profit_centre_fullname);
                    Vessel profitCentreVessel = null;
                    if (bunkerPO != null && profitCentre != null)
                    {
                        profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, bunkerPO.vessel, profitCentre.client);
                        //ChargeTypeAccountMapping redelAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Redel Bunkers ROB");
                        afxvoyageVoyage = m_ud_voyageimp_Service.GetAFXVOYAGEByDimValue(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, bunkerPO.voyage_no.ToString());
                    }
                    //BunkerInventoryManagerMapping biMap = null;
                    //if (profitCentre != null)
                    //    biMap = m_BunkerInventoryManagerMappingService.SearchInsert(bunkerPO.profit_centre_fullname,
                    //    profitCentre.client, profitCentre.agr_description, profitCentre.agr_dim_value, profitCentre.agr_rel_value);
                    if (bunkerPO == null) message += " Bunker PO can not find";
                    if (profitCentre == null) message += " Profit Centre PO can not find";
                    if (profitCentreVessel == null) message += " Profit Centre PO can not find";
                    if (afxvoyageVoyage == null) message += " Unable to find voyage in agresso : " + (profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value) + "-V" + bunkerPO.voyage_no.PadLeft(3, '0'); ;
                    if(message.Trim() != string.Empty)
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, message, "0", "", podetail.remarks);
                        continue;
                    }
                    ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(bunkerPO.operated_by);


                    //----------------------------------------IR validation END----------------------------------------------------------

                    //same batch for same enquiry number -------------------------------
                    batchID = batchID == "" ? "SG" + DateTime.Now.ToString("ddMMyyyyHHmmss") : batchID;
                    batchID_IR = batchID_IR == "" ? "IR" + DateTime.Now.ToString("ddMMyyyyHHmmss") : batchID_IR;
                    if (enquiry_no != grn.enquiry_no)
                    {
                        batchID = "SG" + DateTime.Now.ToString("ddMMyyyyHHmmss");
                        batchID_IR = "IR" + DateTime.Now.ToString("ddMMyyyyHHmmss");
                    }
                    //same batch for same enquiry number -------------------------------

                   //GRN PART-----------------------------------------------
                    algbatchinput algbatchinput_GRN = new algbatchinput();
                    algbatchinput_GRN.batch_id = batchID;
                    algbatchinput_GRN.client =  podetail.client.Trim();
                    algbatchinput_GRN.deliv_date = grn.delivery_Date;
                    algbatchinput_GRN.ext_ord_ref = podetail.ext_ord_ref;
                    algbatchinput_GRN.line_no = podetail.line_no;
                    algbatchinput_GRN.location = "";
                    algbatchinput_GRN.long_info1 = "";
                    algbatchinput_GRN.lot = "";
                    algbatchinput_GRN.order_id = podetail.order_id;
                    algbatchinput_GRN.serial_no = "";
                    algbatchinput_GRN.trans_type = "51";
                    algbatchinput_GRN.value_1 = grn.quantity_delivered;
                    algbatchinput_GRN.voucher_type = "SG";
                    algbatchinput_GRN.warehouse = "";
                    algbatchinput_GRN.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    enquiry_no = grn.enquiry_no;
                  
                    //IR PART------------------------------------------#######################-----------
                    algbatchinput algbatch = new algbatchinput();
                    algbatch.voucher_type = "IR";
                    algbatch.trans_type = "49";
                    algbatch.account = "3334";//15072013 acc.account_grn.Substring(0, 4); //redelAcc.freighthire_account;
                    algbatch.article = acc.account_grn;
                    algbatch.amount = (grn.quantity_delivered == 0 ? bunkerPO.quantity_stemmed : grn.quantity_delivered) * bunkerPO.price_gross;
                    algbatch.amount_set = 1;//27052013
                    algbatch.status = "";//27052013
                    algbatch.batch_id = batchID_IR;
                    algbatch.client = profitCentre.client;
                    //algbatch.deliv_date = grn.delivery_Date;
                    algbatch.order_id = GetOrderRunningNumber();
                    algbatch.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    algbatch.responsible = user.agresso_user;// bi.last_updated_by;
                    algbatch.value_1 = Math.Round(grn.quantity_delivered == 0 ? bunkerPO.quantity_stemmed : grn.quantity_delivered, 4);
                    algbatch.warehouse = profitCentreVessel.agr_dim_value;
                    algbatch.art_descr = grn.grade;// bi.description;
                    algbatch.unit_code = "TON";
                    algbatch.unit_price = bunkerPO.price_gross;
                    algbatch.sequence_no = 0;// GetSequenceRunningNumber(); 27052013
                    algbatch.line_no = 1;// line;
                    algbatch.dim_1 = profitCentreVessel.agr_rel_value;
                    algbatch.dim_4 = "";
                    algbatch.dim_5 = profitCentreVessel.agr_dim_value;
                    algbatch.dim_6 = profitCentre.agr_dim_value;
                    algbatch.delivery_descr = grn.grade;
                    algbatch.deliv_date = afxvoyageVoyage.voy_actual_voy_end;
                    //if (algbatch.dim_6.Trim() != "610") //not pcl shipping
                    //{
                    //    algbatch.account = "2904";
                    //    algbatch.dim_1 = biMap.rel_value_map;//"D6100";
                    //    algbatch.dim_2 = redelAcc.freighthire_account;
                    //    algbatch.dim_6 = biMap.dim_value_map;//"610";
                    //    algbatch.dim_7 = profitCentre.agr_dim_value;
                    //}

                    //GRN PART------------
                    m_AlgBatcInputService.Insert(algbatchinput_GRN);
                    m_AlgBatcInputService.InsertToInterfaceTable(algbatchinput_GRN);                   
                    //IR PART------------
                    m_AlgBatcInputService.Insert(algbatch);
                    m_AlgBatcInputService.InsertToInterfaceTable(algbatch);
                    //  UpdateStatusBunkerInv(bi.BunkerInventory_id, algbatch.batch_id + ";" + algbatch.agrtid.ToString(), "1");
                    UpdateRemarks(grn.i_bunkergrn_id, algbatchinput_GRN.agrtid.ToString(), "1", batchID + ";" + batchID_IR);
                    UpdateRemarksAPODetail(podetail.i_apodetail_id, grn.i_bunkergrn_id.ToString(), "1", batchID + ";" + batchID_IR, podetail.remarks);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();

            }

        }

        public void ExecuteBunkerIRByPO(long orderID, string client)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();
                IList pendingPOs = new ArrayList();
                SqlCommand cmd = new SqlCommand(BunkerGRNSQL.GetAllPendingPOTempByOrderID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@order_id", SqlDbType.BigInt).Value = orderID;
                cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = client;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    apodetail po = apodetail.apodetailFromInterface(rd);
                    pendingPOs.Add(po);
                }
                rd.Close();

                string batchID = "";
                string batchID_IR = "";
                int enquiry_no = 0;

                foreach (apodetail podetail in pendingPOs)
                {
                    // if (podetail.ext_ord_ref != "778_03072013") continue;
                    if (podetail.status.Trim() != "O")
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, "-[E022] PO status waiting to order ", "0", "", podetail.remarks);
                        continue;
                    }
                    //GradeAccountMapping acc = m_gradeAccountMappingService.GetGradeAccountMappingByAccount(podetail.sup_article);
                    GradeAccountMapping acc = m_gradeAccountMappingService.GetGradeAccountMapping(podetail.art_descr.Trim());
                    string enquiry = "";
                    try
                    {
                        enquiry = podetail.ext_ord_ref.Split('_')[0];//get enquiry number
                    }
                    catch { }
                    if (enquiry == "")
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, string.Format("-[E019] Unable to find Enquiry Number '{0}' \r\n", podetail.ext_ord_ref), "0", "", podetail.remarks);
                        continue;
                    }
                    if (acc.account_grn == "")
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, string.Format("-[E019] Unable to find GRN Grade Mapping Item '{0}' \r\n", podetail.sup_article), "0", "", podetail.remarks);
                        continue;
                    }
                    cmd.CommandText = BunkerGRNSQL.GetAllBunkerGRNTempByEnquiryNoValidStatus();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@enquiry_no", SqlDbType.Int).Value = enquiry;
                    cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = podetail.art_descr;// acc.grade;
                    rd = cmd.ExecuteReader();
                    BunkerGRN grn = null;
                    while (rd.Read())
                    {
                        grn = new BunkerGRN(rd, true);
                    }
                    rd.Close();
                    if (grn == null)
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, string.Format("-[E023] Unable to find GRN for PO:'{0}' item: '{1}' - '{2}' \r\n", podetail.order_id, podetail.sup_article, podetail.art_descr), "0", "", podetail.remarks);
                        continue;
                    }

                    //----------------------------------------IR validation----------------------------------------------------------
                    string message = "";
                    BunkerPO bunkerPO = m_BunkerPOService.GetPOByEnquiryStemed(grn.enquiry_no, grn.grade);//, grn.supplier
                    ProfitCentre profitCentre = null;
                    afxvoyage afxvoyageVoyage = null;
                    if (bunkerPO != null)
                        profitCentre = m_profitCentreService.GetProfitCentreMapping(bunkerPO.profit_centre_fullname);
                    Vessel profitCentreVessel = null;
                    if (bunkerPO != null && profitCentre != null)
                    {
                        profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, bunkerPO.vessel, profitCentre.client);
                        //ChargeTypeAccountMapping redelAcc = m_ChargeTypeAccountMappingService.GetChargeTypeAccountMapping("Redel Bunkers ROB");
                        afxvoyageVoyage = m_ud_voyageimp_Service.GetAFXVOYAGEByDimValue(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, bunkerPO.voyage_no.ToString());
                    }
                    //BunkerInventoryManagerMapping biMap = null;
                    //if (profitCentre != null)
                    //    biMap = m_BunkerInventoryManagerMappingService.SearchInsert(bunkerPO.profit_centre_fullname,
                    //    profitCentre.client, profitCentre.agr_description, profitCentre.agr_dim_value, profitCentre.agr_rel_value);
                    ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(bunkerPO.operated_by);
                    if (bunkerPO == null) message += " Bunker PO can not find";
                    if (profitCentre == null) message += " Profit Centre PO can not find";
                    if (profitCentreVessel == null) message += " Profit Centre PO can not find";
                    if (message.Trim() != string.Empty)
                    {
                        UpdateRemarksAPODetail(podetail.i_apodetail_id, message, "0", "", podetail.remarks);
                        continue;
                    }


                    //----------------------------------------IR validation END----------------------------------------------------------

                    //same batch for same enquiry number -------------------------------
                    batchID = batchID == "" ? "SG" + DateTime.Now.ToString("ddMMyyyyHHmmss") : batchID;
                    batchID_IR = batchID_IR == "" ? "IR" + DateTime.Now.ToString("ddMMyyyyHHmmss") : batchID_IR;
                    if (enquiry_no != grn.enquiry_no)
                    {
                        batchID = "SG" + DateTime.Now.ToString("ddMMyyyyHHmmss");
                        batchID_IR = "IR" + DateTime.Now.ToString("ddMMyyyyHHmmss");
                    }
                    //same batch for same enquiry number -------------------------------

                    //GRN PART-----------------------------------------------
                    //algbatchinput algbatchinput_GRN = new algbatchinput();
                    //algbatchinput_GRN.batch_id = batchID;
                    //algbatchinput_GRN.client = podetail.client.Trim();
                    //algbatchinput_GRN.deliv_date = grn.delivery_Date;
                    //algbatchinput_GRN.ext_ord_ref = podetail.ext_ord_ref;
                    //algbatchinput_GRN.line_no = podetail.line_no;
                    //algbatchinput_GRN.location = "";
                    //algbatchinput_GRN.long_info1 = "";
                    //algbatchinput_GRN.lot = "";
                    //algbatchinput_GRN.order_id = podetail.order_id;
                    //algbatchinput_GRN.serial_no = "";
                    //algbatchinput_GRN.trans_type = "51";
                    //algbatchinput_GRN.value_1 = grn.quantity_delivered;
                    //algbatchinput_GRN.voucher_type = "SG";
                    //algbatchinput_GRN.warehouse = "";
                    //enquiry_no = grn.enquiry_no;

                    //IR PART------------------------------------------#######################-----------
                    algbatchinput algbatch = new algbatchinput();
                    algbatch.voucher_type = "IR";
                    algbatch.trans_type = "49";
                    algbatch.account = "3334";//15072013 acc.account_grn.Substring(0, 4); //redelAcc.freighthire_account;
                    algbatch.article = acc.account_grn;
                    algbatch.amount = (grn.quantity_delivered == 0 ? bunkerPO.quantity_stemmed : grn.quantity_delivered) * bunkerPO.price_gross;
                    algbatch.amount_set = 1;//27052013
                    algbatch.status = "";//27052013
                    algbatch.batch_id = batchID_IR;
                    algbatch.client = profitCentre.client;
                    //algbatch.deliv_date = grn.delivery_Date;
                    algbatch.order_id = GetOrderRunningNumber();
                    algbatch.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    algbatch.responsible = user.agresso_user;// bi.last_updated_by;
                    algbatch.value_1 = Math.Round(grn.quantity_delivered == 0 ? bunkerPO.quantity_stemmed : grn.quantity_delivered, 4);
                    algbatch.warehouse = profitCentreVessel.agr_dim_value;
                    algbatch.art_descr = grn.grade;// bi.description;
                    algbatch.unit_code = "TON";
                    algbatch.unit_price = bunkerPO.price_gross;
                    algbatch.sequence_no = 0;// GetSequenceRunningNumber(); 27052013
                    algbatch.line_no = 1;// line;
                    algbatch.dim_1 = profitCentreVessel.agr_rel_value;
                    algbatch.dim_4 = "";
                    algbatch.dim_5 = profitCentreVessel.agr_dim_value;
                    algbatch.dim_6 = profitCentre.agr_dim_value;
                    algbatch.delivery_descr = grn.grade;
                    algbatch.deliv_date = afxvoyageVoyage.voy_actual_voy_end;
                    //if (algbatch.dim_6.Trim() != "610") //not pcl shipping
                    //{
                    //    algbatch.account = "2904";
                    //    algbatch.dim_1 = biMap.rel_value_map;//"D6100";
                    //    algbatch.dim_2 = redelAcc.freighthire_account;
                    //    algbatch.dim_6 = biMap.dim_value_map;//"610";
                    //    algbatch.dim_7 = profitCentre.agr_dim_value;
                    //}

                    //GRN PART------------
                   // m_AlgBatcInputService.Insert(algbatchinput_GRN);
                   // m_AlgBatcInputService.InsertToInterfaceTable(algbatchinput_GRN);
                    //IR PART------------
                    m_AlgBatcInputService.Insert(algbatch);
                    m_AlgBatcInputService.InsertToInterfaceTable(algbatch);
                    //  UpdateStatusBunkerInv(bi.BunkerInventory_id, algbatch.batch_id + ";" + algbatch.agrtid.ToString(), "1");
                    UpdateRemarks(grn.i_bunkergrn_id, grn.remarks, "1", grn.batch_id + ";" + algbatch.batch_id);
                   // UpdateRemarksAPODetail(podetail.i_apodetail_id, grn.i_bunkergrn_id.ToString(), "1", batchID + ";" + batchID_IR, podetail.remarks);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();

            }

        }

        private int GetOrderRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_3) + 1;
            runningNumber.val_3 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        private void UpdateRemarks(int p, string remarks, string pickupflag, string batch_id)
        {
            m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
            SqlCommand cmd = new SqlCommand(BunkerGRNSQL.UpdateGRNTempPickup_flagStatus(), m_dataBaseConectivity.InterfaceSqlConnection_c);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = pickupflag;
            cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@pickup_update_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@agrtidList", SqlDbType.NVarChar).Value = "";
            cmd.Parameters.Add("@i_bunkergrn_id", SqlDbType.Int).Value = p;
            cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = remarks;
            cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batch_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection_c.Close();
        }
        private void UpdateRemarksAPODetail(long p, string remarks, string pickupflag, string batch_id, string oldRemarks)
        {
            if (pickupflag == "0")
                if (remarks == oldRemarks) return;
            m_dataBaseConectivity.InterfaceSqlConnection_c.Open();
            SqlCommand cmd = new SqlCommand(BunkerGRNSQL.UpdatePOTempPickup_flagStatus(), m_dataBaseConectivity.InterfaceSqlConnection_c);
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = pickupflag;
            cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@i_apodetail_id", SqlDbType.BigInt).Value = p;
            cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = remarks;
            cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batch_id;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection_c.Close();
        }
        public void ImportAllDataSoftmarToMiddleLayer()
        {
            try
            {
                IList grns = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllBunkerGRN();
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerGRN grn = new BunkerGRN(rd, false);
                    grns.Add(grn);
                }
                rd.Close();

                IList grnList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllBunkerGRNEnquiry();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    grnList.Add(new BunkerGRN(Convert.ToInt32(rd["enquiry_no"]), rd["grade"].ToString()));
                }
                rd.Close();
                foreach (BunkerGRN ci in grns)
                {
                    cmd.Parameters.Clear();
                    if (!grnList.Contains(ci))//.enquiry_no))
                    {
                        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = ci.vessel_name;
                        cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                        cmd.Parameters.Add("@stemmed_date", SqlDbType.DateTime).Value = ci.stemmed_date;
                        cmd.Parameters.Add("@supplier", SqlDbType.NVarChar).Value = ci.supplier;
                        cmd.Parameters.Add("@enquiry_no", SqlDbType.Int).Value = ci.enquiry_no;
                        cmd.Parameters.Add("@lifting_status", SqlDbType.NVarChar).Value = ci.lifting_status;
                        cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = ci.grade;
                        cmd.Parameters.Add("@quantity_delivered", SqlDbType.Decimal).Value = ci.quantity_delivered;
                        cmd.Parameters.Add("@delivery_Date", SqlDbType.DateTime).Value = ci.delivery_Date;
                        cmd.Parameters.Add("@enquiry_Date", SqlDbType.DateTime).Value = ci.enquiry_Date;
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.CommandText = BunkerGRNSQL.InsertBunkerGRNTemp();
                        cmd.ExecuteNonQuery();
                    }
                    //cmd.CommandText = BunkerGRNSQL.CountBunkerGRNTemp();
                    //cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    //cmd.Parameters.Clear();
                    //cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = ci.vessel_name;
                    //cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                    //cmd.Parameters.Add("@stemmed_date", SqlDbType.DateTime).Value = ci.stemmed_date;
                    //cmd.Parameters.Add("@supplier", SqlDbType.NVarChar).Value = ci.supplier;
                    //cmd.Parameters.Add("@enquiry_no", SqlDbType.Int).Value = ci.enquiry_no;
                    //cmd.Parameters.Add("@lifting_status", SqlDbType.NVarChar).Value = ci.lifting_status;
                    //cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = ci.grade;
                    //cmd.Parameters.Add("@quantity_delivered", SqlDbType.Decimal).Value = ci.quantity_delivered;
                    //cmd.Parameters.Add("@delivery_Date", SqlDbType.DateTime).Value = ci.delivery_Date;
                    //cmd.Parameters.Add("@enquiry_Date", SqlDbType.DateTime).Value = ci.enquiry_Date;
                    //int found = Convert.ToInt16(cmd.ExecuteScalar());
                    //if (found == 0)
                    //{
                    //    cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                    //    cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                    //    cmd.CommandText = BunkerGRNSQL.InsertBunkerGRNTemp();
                    //    cmd.ExecuteNonQuery();
                    //}
                }

                IList pos = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                cmd.CommandText = AgressoSQL.GetPurchaseOrderAll();
                cmd.CommandTimeout = 300;
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    apodetail po =new apodetail(rd);
                    pos.Add(po);
                }
                rd.Close();
                foreach (apodetail poDetails in pos)
                {
                    cmd.CommandText = BunkerGRNSQL.GetApodetailTempByOrderIDSupArticle();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@order_id", SqlDbType.NVarChar).Value = poDetails.order_id;
                    cmd.Parameters.Add("@ext_ord_ref", SqlDbType.NVarChar).Value = poDetails.ext_ord_ref;
                    cmd.Parameters.Add("@line_no", SqlDbType.NVarChar).Value = poDetails.line_no;
                    cmd.Parameters.Add("@sup_article", SqlDbType.NVarChar).Value = poDetails.sup_article;
                    cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = poDetails.client;
                    cmd.Parameters.Add("@art_descr", SqlDbType.NVarChar).Value = poDetails.art_descr;
                    rd = cmd.ExecuteReader();
                    apodetail apo = null;
                    while (rd.Read()) apo = apodetail.apodetailFromInterface(rd);
                    rd.Close();
                   // if (apo.order_id.ToString() == "4500000009")
                   // {

                   //}
                    if (apo == null)
                    {
                        cmd.CommandText = BunkerGRNSQL.InsertApodetailTemp();                        
                        cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = poDetails.status;
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        if (apo.status.Trim() == poDetails.status.Trim())
                            continue;
                        cmd.CommandText = BunkerGRNSQL.UpdateApodetailStatus();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = poDetails.status;
                        cmd.Parameters.Add("@i_apodetail_id", SqlDbType.BigInt).Value = apo.i_apodetail_id;
                        cmd.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }


        public void ImportAllDataSoftmarToMiddleLayer(int enquiryNo)
        {
            try
            {
                IList grns = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllBunkerGRNByEnquiryID();
                cmd.Parameters.Add("@EnquiryID", SqlDbType.Int).Value = enquiryNo;
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    BunkerGRN grn = new BunkerGRN(rd, false);
                    grns.Add(grn);
                }
                rd.Close();

                IList grnList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllBunkerGRNEnquiry();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    grnList.Add(new BunkerGRN(Convert.ToInt32(rd["enquiry_no"]), rd["grade"].ToString()));
                }
                rd.Close();
                foreach (BunkerGRN ci in grns)
                {
                    cmd.Parameters.Clear();
                    if (!grnList.Contains(ci))//.enquiry_no))
                    {
                        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = ci.vessel_name;
                        cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = ci.voyage_no;
                        cmd.Parameters.Add("@stemmed_date", SqlDbType.DateTime).Value = ci.stemmed_date;
                        cmd.Parameters.Add("@supplier", SqlDbType.NVarChar).Value = ci.supplier;
                        cmd.Parameters.Add("@enquiry_no", SqlDbType.Int).Value = ci.enquiry_no;
                        cmd.Parameters.Add("@lifting_status", SqlDbType.NVarChar).Value = ci.lifting_status;
                        cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = ci.grade;
                        cmd.Parameters.Add("@quantity_delivered", SqlDbType.Decimal).Value = ci.quantity_delivered;
                        cmd.Parameters.Add("@delivery_Date", SqlDbType.DateTime).Value = ci.delivery_Date;
                        cmd.Parameters.Add("@enquiry_Date", SqlDbType.DateTime).Value = ci.enquiry_Date;
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.CommandText = BunkerGRNSQL.InsertBunkerGRNTemp();
                        cmd.ExecuteNonQuery();
                    }
                }

                //IList pos = new ArrayList();
                //cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                //cmd.CommandText = AgressoSQL.GetPurchaseOrderAll();
                //cmd.CommandTimeout = 300;
                //rd = cmd.ExecuteReader();
                //while (rd.Read())
                //{
                //    apodetail po = new apodetail(rd);
                //    pos.Add(po);
                //}
                //rd.Close();
                //foreach (apodetail poDetails in pos)
                //{
                //    cmd.CommandText = BunkerGRNSQL.GetApodetailTempByOrderIDSupArticle();
                //    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                //    cmd.Parameters.Clear();
                //    cmd.Parameters.Add("@order_id", SqlDbType.NVarChar).Value = poDetails.order_id;
                //    cmd.Parameters.Add("@ext_ord_ref", SqlDbType.NVarChar).Value = poDetails.ext_ord_ref;
                //    cmd.Parameters.Add("@line_no", SqlDbType.NVarChar).Value = poDetails.line_no;
                //    cmd.Parameters.Add("@sup_article", SqlDbType.NVarChar).Value = poDetails.sup_article;
                //    cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = poDetails.client;
                //    cmd.Parameters.Add("@art_descr", SqlDbType.NVarChar).Value = poDetails.art_descr;
                //    rd = cmd.ExecuteReader();
                //    apodetail apo = null;
                //    while (rd.Read()) apo = apodetail.apodetailFromInterface(rd);
                //    rd.Close();
                //    if (apo == null)
                //    {
                //        cmd.CommandText = BunkerGRNSQL.InsertApodetailTemp();
                //        cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = poDetails.status;
                //        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                //        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                //        cmd.ExecuteNonQuery();
                //    }
                //    else
                //    {
                //        if (apo.status.Trim() == poDetails.status.Trim())
                //            continue;
                //        cmd.CommandText = BunkerGRNSQL.UpdateApodetailStatus();
                //        cmd.Parameters.Clear();
                //        cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = poDetails.status;
                //        cmd.Parameters.Add("@i_apodetail_id", SqlDbType.BigInt).Value = apo.i_apodetail_id;
                //        cmd.ExecuteNonQuery();
                //    }
                //}

            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void ImportAllAgressoPOToMiddleLayer()
        {
            try
            {
                IList grns = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Open();

                SqlCommand cmd = new SqlCommand();
                IList pos = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.AgrTempConnectionSQLConnection;
                cmd.CommandText = AgressoSQL.GetPurchaseOrderAll();
                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    apodetail po = new apodetail(rd);
                    pos.Add(po);
                }
                rd.Close();
                foreach (apodetail poDetails in pos)
                {
                    cmd.CommandText = BunkerGRNSQL.GetApodetailTempByOrderIDSupArticle();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@order_id", SqlDbType.NVarChar).Value = poDetails.order_id;
                    cmd.Parameters.Add("@ext_ord_ref", SqlDbType.NVarChar).Value = poDetails.ext_ord_ref;
                    cmd.Parameters.Add("@line_no", SqlDbType.NVarChar).Value = poDetails.line_no;
                    cmd.Parameters.Add("@sup_article", SqlDbType.NVarChar).Value = poDetails.sup_article;
                    cmd.Parameters.Add("@client", SqlDbType.NVarChar).Value = poDetails.client;
                    cmd.Parameters.Add("@art_descr", SqlDbType.NVarChar).Value = poDetails.art_descr;
                    rd = cmd.ExecuteReader();
                    apodetail apo = null;
                    while (rd.Read()) apo = apodetail.apodetailFromInterface(rd);
                    rd.Close();
                    if (apo == null)
                    {
                        cmd.CommandText = BunkerGRNSQL.InsertApodetailTemp();
                        cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = poDetails.status;
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        if (apo.status.Trim() == poDetails.status.Trim())
                            continue;
                        cmd.CommandText = BunkerGRNSQL.UpdateApodetailStatus();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@status", SqlDbType.NVarChar).Value = poDetails.status;
                        cmd.Parameters.Add("@i_apodetail_id", SqlDbType.BigInt).Value = apo.i_apodetail_id;
                        cmd.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.AgrTempConnectionSQLConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetAllPendingPendingBunkerGRN()
        {
            IList bunkerPO = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllPendingPendingBunkerGRN();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    bunkerPO.Add(new BunkerGRN(rd));
                }
                rd.Close();
                return bunkerPO;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingPendingBunkerGRN(string p)
        {
            IList bunkerPO = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllPendingPendingBunkerGRN(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    bunkerPO.Add(new BunkerGRN(rd));
                }
                rd.Close();
                return bunkerPO;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingPendingPendingPO()
        {
            IList pos = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllPendingPendingPendingPO();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    pos.Add(apodetail.apodetailFromInterfacePending(rd));
                }
                rd.Close();
                return pos;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public int GetAllPendingPendingPendingPOCount()
        {
            int pos = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllPendingPendingPendingPOCount();
                pos = Convert.ToInt32(cmd.ExecuteScalar());
                return pos;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingPendingPendingPO(string p)
        {
            IList pos = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = BunkerGRNSQL.GetAllPendingPendingPendingPO(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    pos.Add(apodetail.apodetailFromInterfacePending(rd));
                }
                rd.Close();
                return pos;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
